﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBackupSnapshotCreate]
#-- Purpose:		Creates a snapshot of a database.  Purpose is to be used
#--					in a night batch process to make periodic snapshots throughout
#--					the night to be used for quick restores
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBackupSnapshotCreate]
(
	@database_name			sysname,
	@label					sysname
)
AS

SET NOCOUNT ON 

--- Validate Parameters
IF ISNULL(@database_name, '') = '' OR ISNULL(@label, '') = ''
  BEGIN
	RAISERROR ('All parameters are required to create a backup snapshot', 16, 1)
	RETURN
  END
IF NOT EXISTS (SELECT TOP 1 * FROM master.sys.databases WHERE name = @database_name)
  BEGIN
	RAISERROR ('The database you requested a snapshot of does not exist', 16, 1)
	RETURN
  END

--- Declare Local Variables
DECLARE		@snap_db_name sysname, @sSQL nvarchar(MAX), @file_name sysname, @directory_name nvarchar(MAX),
			@i int, @is_enterprise bit
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spBackupSnapshotCreate'

--- Initialize Local Variables
EXEC		@is_enterprise = dbo.spIsEnterpriseSQL
SELECT		@database_name = LTRIM(RTRIM(@database_name)),
			@label = LTRIM(RTRIM(@label)),
			@snap_db_name = 'SNAP_' + @database_name + '_' + @label,
			@is_enterprise = ISNULL(@is_enterprise, 0)

--- Snapshots are only supported on the Developer/Enterprise Edition
IF @is_enterprise <> 1
  BEGIN
	RAISERROR ('Snapshots can only be used in Developer or Enterprise Edition', 16, 1)
	RETURN
  END

--- Drop the snapshot if it already exists
IF EXISTS (SELECT TOP 1 * FROM master.sys.databases WHERE name = @snap_db_name)
  BEGIN
	EXEC dbo.spBackupSnapshotDrop @database_name = @database_name, @label = @label
  END

--- Build the statement to create the snapshot
SELECT	@sSQL = 'CREATE DATABASE [' + @snap_db_name + '] ON ',
		@i = 1

DECLARE crs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
	SELECT	f.name,
			dbo.DirectoryName(f.physical_name) directory_name
	FROM	master.sys.master_files f
	JOIN	master.sys.databases d ON f.database_id = d.database_id
	WHERE	d.name = @database_name
			AND f.type = 0
OPEN crs
FETCH NEXT FROM crs INTO  @file_name, @directory_name
WHILE @@FETCH_STATUS <> -1
  BEGIN
	IF @i > 1
		SELECT	@sSQL = @sSQL + ', '
		
	SELECT	@sSQL = @sSQL + '(NAME = N''' + @file_name + ''', FILENAME = N''' + @directory_name + '\' + @snap_db_name + '_' + @file_name + '_' + CAST(@i as varchar(10)) + ''')',
			@i = @i + 1
	
	FETCH NEXT FROM crs INTO  @file_name, @directory_name
  END
CLOSE crs
DEALLOCATE crs

--- Create the snapshot
SELECT	@sSQL = @sSQL + ' AS SNAPSHOT OF ' + @database_name
EXEC	(@sSQL)

--- Create a log entry that a snapshot was created
EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sSQL

SET NOCOUNT OFF
